package com.eve.crp;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.eve.DAObjects.ClientDAO;
import com.eve.DAObjects.ClientRowMapper;

public class JdbcClientDAO extends JdbcDaoSupport implements ClientDAO, InitializingBean{
	Client client;
	
	@Override
	public void createCase(Client client) throws SQLException{
		this.client = client;
		client.setFirstname(client.getFirstname().toUpperCase());
		client.setLastname(client.getLastname().toUpperCase());
		Long peopleId = this.storePerson();
		Long caseId = this.createCase(peopleId);
		this.addAddress(caseId);
	}
	
	@Override
	public ArrayList<Client> getCaseList(ClientFilter filter){
		System.out.println("Get Case List called");
		ArrayList<Client> clientList = new ArrayList<Client>();
		Calendar cal = Calendar.getInstance();
		Date date = new Date();
		cal.add(Calendar.DATE, Calendar.MONTH -1);
		date.setTime(cal.getTimeInMillis());
		//Date nowDate = new Date();
		String query = "";
			query = "select FirstName, LastName, Birthdate, Disabled, Veteran, NeedDate, EnteredDate, Street, City, State, Zip, NeedID, a.NeedDescription, b.NumOfChildren from crp.`case` a "+
					" JOIN crp.people b ON a.PeopleID = b.PersonID"+ 
					" JOIN crp.addresscasecrosswlk c ON c.CaseID = a.CaseID"+
					" JOIN crp.address d ON d.AddressID = c.AddressID";
			System.out.println("Query is: "+query);
			 clientList = (ArrayList<Client>) getJdbcTemplate().query(query, new ClientRowMapper());
		return clientList;
	}
	
	private Long storePerson() throws SQLException{
		Long lastId = 0L;
		String query = "SELECT count(*) FROM crp.people WHERE FirstName = ? AND LastName = ? AND Birthdate = ?";
		int result = getJdbcTemplate().queryForInt(query, new Object[] { client.getFirstname(), client.getLastname(), client.getBirthdate()});
		if(result == 0) {
			query = "INSERT INTO crp.people(FirstName,LastName,Birthdate,Disabled,Veteran,NumOfChildren) VALUES (?,?,?,?,?,?)";
			PreparedStatement ps = getJdbcTemplate().getDataSource().getConnection().prepareStatement(query,PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, client.getFirstname());
			ps.setString(2, client.getLastname());
			ps.setDate(3, new java.sql.Date(client.getBirthdate().getTime()));
			ps.setBoolean(4, client.getDisabled());
			ps.setBoolean(5, client.getVeteran());
			ps.setInt(6, client.getNumOfChildren());
			ps.execute();
			ResultSet rs = ps.getGeneratedKeys();
			rs.first();
			lastId = rs.getLong(1);
			rs.close();
			ps.close();
		} else {
			query = "SELECT PersonID from crp.people WHERE FirstName = ? AND LastName = ? AND Birthdate = ?";
			lastId = getJdbcTemplate().queryForLong(query, new Object[] { client.getFirstname(), client.getLastname(), client.getBirthdate()});
			query = "DELETE FROM crp.people WHERE PersonID = ?";
			getJdbcTemplate().update(query,lastId);
			query = "INSERT INTO crp.people(PersonID, FirstName,LastName,Birthdate,Disabled,Veteran) VALUES (?,?,?,?,?,?)";
			getJdbcTemplate().update(query, new Object[] {lastId, client.getFirstname(),client.getLastname(),client.getBirthdate(),client.getDisabled(),client.getVeteran()});
		}
		return lastId;
	}
	private Long createCase(Long peopleId) throws SQLException{
		String query = "INSERT INTO crp.case (NeedID,NeedDescription,PeopleID,needDate) VALUES (?,?,?,?)";
		PreparedStatement ps = getJdbcTemplate().getDataSource().getConnection().prepareStatement(query,PreparedStatement.RETURN_GENERATED_KEYS);
		ps.setInt(1, client.getNeedCategory());
		ps.setString(2, client.getNeedDescription());
		ps.setLong(3, peopleId);
		ps.setDate(4, new java.sql.Date(client.getNeedDate().getTime()));
		ps.execute();
		ResultSet rs = ps.getGeneratedKeys();
		rs.first();
		Long returnable = rs.getLong(1);
		rs.close();
		ps.close();
		return returnable;
	}
	
	private void addAddress(Long caseId) throws SQLException{
		String query = "SELECT count(*) FROM crp.address WHERE Street = ? AND City = ? AND Zip = ?";
		int result = getJdbcTemplate().queryForInt(query, new Object[] {client.getStreet(),client.getCity(),client.getZip()});
		if(result == 0) {
			query = "INSERT INTO crp.address (CaseID, City, State, Zip, Street) VALUES (?,?,'WV',?,?)";
			PreparedStatement ps = getJdbcTemplate().getDataSource().getConnection().prepareStatement(query,PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1, caseId);
			ps.setInt(2, client.getCity());
			ps.setInt(3, client.getZip());
			ps.setString(4, client.getStreet());
			ps.execute();
			ResultSet rs = ps.getGeneratedKeys();
			rs.first();
			Long addressId = rs.getLong(1);
			rs.close();
			ps.close();
			query = "INSERT INTO crp.addresscasecrosswlk values (?,?)";
			getJdbcTemplate().update(query,new Object[] {caseId,addressId});
		} else {
			query = "SELECT AddressID FROM crp.address WHERE Street = ? AND City = ? AND Zip = ?";
			Long addressId = getJdbcTemplate().queryForLong(query,new Object[] {client.getStreet(),client.getCity(),client.getZip()});
			query = "INSERT INTO crp.addresscasecrosswlk values (?,?)";
			getJdbcTemplate().update(query,new Object[] {caseId,addressId});
		}	
	}
}
